package com.soft.dao;

import java.sql.SQLException;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;
import com.soft.domain.User;

public class UserDao {
	private static UserDao dao=new UserDao();
	private static Connection con=null;
	private static Statement st=null;
	private static PreparedStatement ps=null;
	private static ResultSet rs=null;
	public boolean find(String username) throws SQLException{
		con=DB.connection(con);
		String sql="select username from Users";
		st=(Statement) con.createStatement();
		rs=(ResultSet) st.executeQuery(sql);
		while(rs.next()){
			if(username.equals(rs.getString("username"))){
				return true;
			}
		}
		return false;
	}
	
	public User getUser(String username,String password) throws SQLException{
		con=DB.connection(con);
		String sql="select * from Users;";
		st=(Statement) con.createStatement();
		rs=(ResultSet) st.executeQuery(sql);
		while(rs.next()){
			if(username.equals(rs.getString("username")) && password.equals(rs.getString("password"))){
				User user=new User();
				user.setId(rs.getString("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));

				return user;
				
			}
		}
		return null;
	}
	
	public User getUser(String id) throws SQLException{
		con=DB.connection(con);
		String sql="select * from Users;";
		st=(Statement) con.createStatement();
		rs=(ResultSet) st.executeQuery(sql);
		while(rs.next()){
			if(id.equals(rs.getString("id"))){
				User user=new User();
				user.setId(rs.getString("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));

				return user;
				
			}
		}
		return null;
	}
	
	public boolean find(String username,String password) throws SQLException{
		con=DB.connection(con);
		String sql="select * from Users;";
		st=(Statement) con.createStatement();
		rs=(ResultSet) st.executeQuery(sql);
		while(rs.next()){
			if(username.equals(rs.getString("username")) && password.equals(rs.getString("password"))){
				return true;		
			}
		}
		return false;
	}
	public boolean delete(User user) throws SQLException{
		con=DB.connection(con);
		if(dao.find(user.getUsername())){
			String sql="delete from Users where username='"+user.getUsername()+"';";
			st=(Statement) con.createStatement();
			st.executeUpdate(sql);
			return true;
		}
		
		return false;
	}
	
	public boolean update(User user) throws SQLException{
		con=DB.connection(con);
		if(dao.find(user.getUsername())){
			String sql1="update Users set username='"+user.getUsername()+"' where id='"+user.getId()+"';";
			String sql2="update Users set password='"+user.getPassword()+"' where id='"+user.getId()+"';";
			st=(Statement) con.createStatement();
			st.executeUpdate(sql1);
			st.executeUpdate(sql2);
			return true;
		}
		return false;
	}
	
	public boolean add(User user) throws SQLException{
		con=DB.connection(con);
		String sql="insert into Users(id,username,password) values(?,?,?)";
		try {
			ps=(PreparedStatement) con.prepareStatement(sql);
			ps.setString(1, user.getId());
			ps.setString(2, user.getUsername());
			ps.setString(3, user.getPassword());
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
}
